use master
go

create database Student
on
(
	name=Student,
	filename='E:\SQL\330\Student.mdf',
	size=5MB,
	maxsize=10MB,
	filegrowth=10%
)
log on
(
	name=Student_log,
	filename='E:\SQL\330\Student_log.ldf',
	size=5MB,
	maxsize=10MB,
	filegrowth=10%
)
go

use Student
go

create table stuInfo
(
	stuID int identity(1,1),
	stuName varchar(30),
	stuAge varchar(5),
	stuSex varchar(6),
	time datetime
)
go

insert into stuInfo(stuName,stuAge,stuSex)
select 'Tom',19,1 union
select 'Jack',20,0 union
select 'Rose',21,1 union
select 'Lulu',19,1 union
select 'Lili',21,0 union
select 'abc',20,1
go

update stuInfo set time='2007-01-07 01:11:36.590' where stuID=6
go

create table courseInfo
(
	courseID int identity(1,1),
	courseName varchar(10),
	courseMarks varchar(6)
)
go

insert into courseInfo(courseName,courseMarks)values('JavaBase',4),('HTML',2),('JavaScript',2),('SqlBase',2)
go

create table scoreInfo
(
	scoreID int identity(1,1),
	stuID int,
	courseID int,
	score int,
)
go

insert into scoreInfo(stuID,courseID,score)
select 1,1,80 union
select 1,2,85 union
select 1,4,50 union
select 2,1,75 union
select 2,3,45 union
select 2,4,75 union
select 3,1,45 union
select 4,1,95 union
select 4,2,75 union
select 4,3,90 union
select 4,4,45
go

--题目：
--1.查询出每个学生 所选修的课程的数量 和所选修的课程的考试的平均分

select  stuName 学生姓名,count(s.stuID)课程数量,AVG(score)考试平均分 from scoreInfo s
inner join stuInfo i on i.stuID=s.stuID
group by s.stuID,stuName

--2.查询出每门课程的选修的学生的个数和学生考试的总分

select courseName 课程名称,COUNT(s.courseID)学生个数,SUM(score)考试总分 from scoreInfo s 
inner join courseInfo c on c.courseID=s.courseID
group by s.courseID,courseName


--3.查询出性别一样并且年龄一样的学生的信息

select * from stuInfo A 
left join stuInfo B on B.stuID = A.stuID
where B.stuAge=A.stuAge and A.stuSex=B.stuSex

--4.查询出学分一样的课程信息 

select * from courseInfo A
right join courseInfo B on B.courseMarks=A.courseMarks
 
--5.查询出参加了考试的学生的学号，姓名，课程号和分数
select A.stuID,stuName,courseID,score from stuInfo A
right join scoreInfo B on A.stuID=B.stuID

--6.查询出参加了考试的学生的学号，课程号，课程名，课程学分和分数

select A.stuID 学生学号,C.courseID 课程号,C.courseName,C.courseMarks,score from stuInfo A
right join scoreInfo B on A.stuID=B.stuID
left join courseInfo C on C.courseID=B.courseID

--7.查询出没有参加考试的学生的学号和姓名
select B.stuID,B.stuName from scoreInfo A
right join stuInfo B on A.stuID=B.stuID
except
select A.stuID,stuName from stuInfo A
right join scoreInfo B on A.stuID=B.stuID
left join courseInfo C on C.courseID=B.courseID

--8.查询出是周六周天来报到的学生


--9.查询出姓名中有字母a的学生的信息

select * from stuInfo where stuName like '%a%'

--10.查询出选修了2门课程以上的并且考试平均分在70以上的学生的 学号 和 考试平均分以及 选修课程的数量

select stuID 学生学号,AVG(score)考试平均分,COUNT(stuID)课程数量 from scoreInfo
group by stuID
having COUNT(stuID)>2 and AVG(score)>70